﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DAMBAOCLPM
{
    public partial class frmXemketquatonghop : Form
    {
        public frmXemketquatonghop()
        {
            InitializeComponent();
        }

        private void frmXemketquatonghop_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'qlctDataSet.DONVI' table. You can move, or remove it, as needed.
            this.dONVITableAdapter.Fill(this.qlctDataSet.DONVI);
            this.Width = clsVariables.Width_This - 21;
            this.Height = clsVariables.Height_This - clsVariables.Height_TopMenu - 65;
            this.Top = clsVariables.Height_TopMenu - 66;
            this.Left = 0;

            SqlDataAdapter da = new SqlDataAdapter("select distinct SBD from CHITIETBAILAM", clsConnection.con);
            DataTable tb = new DataTable();
            da.Fill(tb);
            cbosbd.DataSource = tb;
            cbosbd.DisplayMember = "SBD";
            cbosbd.ValueMember = "SBD";
            cbosbd.Text = "";
            cbodonvi.Text = "";

        }

        private void cbosbd_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlDataAdapter da = new SqlDataAdapter("select DISTINCT MADE from CHITIETBAILAM  where SBD = '" + cbosbd.SelectedValue.ToString() + "'", clsConnection.con);
            DataTable tb = new DataTable();
            da.Fill(tb);
            cbomade.DataSource = tb;
            cbomade.DisplayMember = "MADE";
            cbomade.ValueMember = "MADE";
            cbomade.Text = "";
            
        }

        private void btnxemketqua_Click(object sender, EventArgs e)
        {
            clsConnection.CreateConnection();         
            string sql1 = "SELECT D.TENKHOA, T.SBD, C.MADE FROM DONVI D, THISINH T, CHITIETBAILAM C, MON M WHERE D.MAKHOA = T.MAKHOA AND T.SBD = C.SBD";
            SqlDataAdapter da1 = new SqlDataAdapter(sql1, clsConnection.con);
            DataSet ds1 = new DataSet();
            da1.Fill(ds1, "table1");
            DataTable table1 = ds1.Tables[0];
         
            if (cbomade.Text != "" && cbosbd.Text != "")
            {
               // dr.Read();

                if (cbodonvi.Text != "")
                {
                       //truy van 3 dua
                        try
                        {
                            SqlDataAdapter da = new SqlDataAdapter("Select T.HOLOT, T.TEN, T.NGAYSINH, T.NOISINH, C.MADE, M.TENMON, COUNT(C.MADE) AS SOCAUDUNG, SUM(CH.SODIEM) AS DIEM FROM THISINH T, CHITIETBAILAM C, CHITIETDAPAN CH, MON M WHERE T.SBD = C.SBD AND C.MADE = CH.MADE AND C.STTCAU = CH.STTCAUDETHI AND C.PHUONGAN = CH.PHUONGAN  AND M.MAMON = C.MAMON AND C.SBD = '" + cbosbd.SelectedValue.ToString() + "' AND C.MADE = '" + cbomade.SelectedValue.ToString() + "' group by T.HOLOT, T.TEN, T.NGAYSINH, T.NOISINH, C.MADE, M.TENMON, C.MADE, CH.SODIEM", clsConnection.con);
                            DataSet ds = new DataSet();
                            da.Fill(ds, "table");
                            DataTable table = ds.Tables[0];
                            dtvXemketqua.DataSource = table;
                        }
                        catch
                        {
                            MessageBox.Show("3 dữ liệu đưa vào có dữ liệu bị sai !");
                        }
                }
                
                else
                {
                    //truy van 2 dua
                    try
                    {
                        SqlDataAdapter da = new SqlDataAdapter("Select T.HOLOT, T.TEN, T.NGAYSINH, T.NOISINH, C.MADE, M.TENMON ,COUNT(C.MADE) AS SOCAUDUNG, SUM(CH.SODIEM) AS DIEM FROM THISINH T, CHITIETBAILAM C, CHITIETDAPAN CH, MON M WHERE T.SBD = C.SBD AND C.MADE = CH.MADE AND C.STTCAU = CH.STTCAUDETHI AND C.PHUONGAN = CH.PHUONGAN  AND M.MAMON = C.MAMON AND C.SBD = '" + cbosbd.SelectedValue.ToString() + "' AND C.MADE = '" + cbomade.SelectedValue.ToString() + "' group by T.HOLOT, T.TEN, T.NGAYSINH, T.NOISINH, C.MADE, M.TENMON, C.MADE, CH.SODIEM", clsConnection.con);
                        DataSet ds = new DataSet();
                        da.Fill(ds, "table");
                        DataTable table = ds.Tables[0];

                        dtvXemketqua.DataSource = table;
                    }
                    catch
                    {
                        MessageBox.Show("SBD và mã đề bị sai ! ");
                    }

                }
                
                
            }
            else
            {
                if (cbodonvi.Text != "")
                {
                    //truy van thang don vi
                    try
                    {
                        SqlDataAdapter da = new SqlDataAdapter("Select T.HOLOT, T.TEN, T.NGAYSINH, T.NOISINH, C.MADE, M.TENMON, COUNT(C.MADE) AS SOCAUDUNG, SUM(CH.SODIEM) AS DIEM FROM THISINH T, CHITIETBAILAM C, CHITIETDAPAN CH, DONVI D, MON M WHERE T.SBD = C.SBD AND C.MADE = CH.MADE AND C.STTCAU = CH.STTCAUDETHI AND C.PHUONGAN = CH.PHUONGAN  AND M.MAMON = C.MAMON AND D.MAKHOA = T.MAKHOA AND D.MAKHOA = '" + cbodonvi.SelectedValue.ToString() + "' group by T.HOLOT, T.TEN, T.NGAYSINH, T.NOISINH, C.MADE, M.TENMON, C.MADE, CH.SODIEM", clsConnection.con);
                        DataSet ds = new DataSet();
                        da.Fill(ds, "table");
                        DataTable table = ds.Tables[0];
                        dtvXemketqua.DataSource = table;
                    }
                    catch
                    {
                        MessageBox.Show("Sai don vi !");
                    }
                }
                else
                {
                    MessageBox.Show("chưa nhập SBD, mã đề, Đơn vị !");                 
                 
                }
            }

            for (int i = 0; i < dtvXemketqua.Rows.Count; i++)
            {
                 dtvXemketqua[7, i].Style.ForeColor = Color.Red;                
            }
        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

    }
}
